package happy.town.threebook;

import java.sql.*;
import java.util.ArrayList;

/**
 * @BelongsProject: threeBook
 * @BelongsPackage: happy.town.threeBook
 * @Author: ByHappy  (Email:1300985301@qq.com)
 * @CreateTime: 2023-08-09 01:02
 * @Description:
 * @Version: 1.0
 **/
public class BookMD {

    /**
     * 1.查询——根据书籍编号查询书籍
     *
     * @param bId
     * @return happy.town.threebook.BookDTO
     * @author ByHappy
     * @date 18:51 2023/8/19
     **/
    public static BookDTO findBookByBno(int bId) {
        Connection conn = null;
        Statement stat = null;
        BookDTO b = null;
        try {
            // 加载驱动：
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接：
            String url = "jdbc:mysql://localhost:3306/20230704_book?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);
            // 创建会话：
            stat = conn.createStatement();
            // 发送SQL：ResultSet结果集合 - 结果集
            ResultSet rs = stat.executeQuery("select*from t_book where bId = " + bId);
            // 处理结果：
            // 如果结果只有一条，想要获取的话，写if就可以,如果是多条，写while
            if (rs.next()) {
                // 先将结果做接收
                int Id = rs.getInt("bId");
                String name = rs.getString("bName");
                String author = rs.getString("bAuthor");
                double price = rs.getDouble("bPrice");

                b = new BookDTO();
                b.setBId(Id);
                b.setBName(name);
                b.setBAuthor(author);
                b.setBPrice(price);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {

                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {

                }
            }
        }
        return b;
    }

    /**
     * 3.查询——所有书籍
     *
     * @return java.util.ArrayList
     * @author ByHappy
     * @date 18:52 2023/8/19
     **/
    public static ArrayList findBooks() {
        Connection conn = null;
        Statement stat = null;

        ArrayList list = new ArrayList();
        try {
            // 加载驱动：
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接：
            String url = "jdbc:mysql://localhost:3306/20230704_book?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);
            // 创建会话：
            stat = conn.createStatement();
            // 发送SQL：ResultSet结果集合 - 结果集
            ResultSet rs = stat.executeQuery("select*from t_book");
            // 处理结果：
            // 如果结果只有一条，想要获取的话，写if就可以,如果是多条，写while,因为查询的是所有，所以用while
            while (rs.next()) {
                // 先将结果做接收
                int Id = rs.getInt("bId");
                String name = rs.getString("bName");
                String author = rs.getString("bAuthor");
                double price = rs.getDouble("bPrice");

                BookDTO b = new BookDTO();
                b.setBId(Id);
                b.setBName(name);
                b.setBAuthor(author);
                b.setBPrice(price);

                list.add(b);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {

                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {

                }
            }
        }
        return list;
    }

    /**
     * 2.添加书籍
     *
     * @param bookDTO
     * @return int
     * @author ByHappy
     * @date 11:36 2023/8/20
     **/
    public static int insertBook(BookDTO bookDTO) {
        Connection conn = null;
        Statement stat = null;
        int result = 0;
        try {
            // 1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2.获取数据库连接
            String url = "jdbc:mysql://localhost:3306/20230704_book?useSSL=false&useUnicode=true&characterEncoding=utf-8";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);

            // 3.获取执行者对象
            stat = conn.createStatement();

            // 4.执行sql语句，并且接收返回的结果集
            String sql = "INSERT INTO t_book VALUES ('" + bookDTO.getBId() + "','" + bookDTO.getBName() + "','" + bookDTO.getBAuthor() + "','" + bookDTO.getBPrice() + "')";
            result = stat.executeUpdate(sql);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {

                }
            }
        }
        // 6.将结果返回
        return result;
    }

    /**
     * 4.根据bId删除书籍
     *
     * @param bId
     * @return int
     * @author ByHappy
     * @date 11:37 2023/8/20
     **/
    public static int deleteBook(Integer bId) {
        Connection conn = null;
        Statement stat = null;
        int result = 0;
        try {
            // 1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2.获取数据库连接
            String url = "jdbc:mysql://localhost:3306/20230704_book?useSSL=false&useUnicode=true&characterEncoding=utf-8";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);

            // 3.获取执行者对象
            stat = conn.createStatement();

            // 4.执行sql语句，并且接收返回的结果集
            String sql = "DELETE FROM t_book WHERE bId='" + bId + "'";
            result = stat.executeUpdate(sql);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        // 6.将结果返回
        return result;
    }
}
